//
//  SqlUtil.m
//  NewsReader
//
//  Created by blacksun on 10-10-19.
//  Copyright 2010 __MyCompanyName__. All rights reserved.
//

#import "SqlUtil.h"


@implementation SqlUtil
+ (sqlite3 *)openDatabse{
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentPath = [paths objectAtIndex:0];
	NSString *dataFilePath = [documentPath stringByAppendingPathComponent:kSqlFilePath];
	
	sqlite3 *database;
	if (sqlite3_open([dataFilePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0,@"Failed to open database");
	}
	return database;
}


+ (void)createFeedTable{
	sqlite3 *database = [SqlUtil openDatabse];
	char *errorMsg1;
	NSString *createSql1 = @"CREATE TABLE IF NOT EXISTS NEW_FEEDS(FEEDID TEXT PRIMARY KEY,SECTIONINDEX INTEGER,SUBJECTINDEX INTEGER, SECTIONID TEXT,SUBJECTID TEXT,LAYOUTSCALE INTEGER,TITLE TEXT,AUTHOR TEXT,PUBDATE TEXT,CREATEDATE TEXT,DESCRIPTION TEXT,LINK TEXT,COMPLETE INTEGER,THUMB TEXT,INSERTDATE INTEGER)";
	if (sqlite3_exec(database, [createSql1 UTF8String], NULL, NULL, &errorMsg1) != SQLITE_OK) {
		sqlite3_close(database);
		//NSAssert(0,@"Error create table: %s",errorMsg1);
	}
	char *errorMsg2;
	NSString *createSql2 = @"CREATE TABLE IF NOT EXISTS NEW_CONTENTS(CONTENTID INTEGER PRIMARY KEY,FEEDID TEXT,HEIGTH INTEFER,WIDTH INTEFER,TYPE TEXT,SRC TEXT,CONTENT TEXT,INSERTDATE INTEGER)";
	if (sqlite3_exec(database, [createSql2 UTF8String], NULL, NULL, &errorMsg2) != SQLITE_OK) {
		sqlite3_close(database);
		//NSAssert(0,@"Error create table: %s",errorMsg2);
	}
	[createSql1 release];
	[createSql2 release];
	sqlite3_close(database);
}


+ (NSString *) buildSelectFeedSqlWithSection:(NSString *)sectionId from:(long) from to:(long) to{
    NSString *selectSql = [[[NSString alloc] initWithFormat:@"SELECT FEEDID,SECTIONINDEX,SUBJECTINDEX,TITLE,AUTHOR,PUBDATE,CREATEDATE,DESCRIPTION,LINK,COMPLETE, THUMB FROM NEW_FEEDS WHERE SECTIONID='%@' AND SECTIONINDEX>%d AND SECTIONINDEX<=%d  ORDER BY SECTIONINDEX DESC;",sectionId,from,to] autorelease];
	
    return selectSql;
}
+ (NSString *) buildSelectFeedSqlWithSubject:(NSString *)subjectId from:(long) from to:(long) to{
    NSString *selectSql = [[[NSString alloc] initWithFormat:@"SELECT FEEDID,SECTIONINDEX,SUBJECTINDEX,TITLE,AUTHOR,PUBDATE,CREATEDATE,DESCRIPTION,LINK,COMPLETE, THUMB FROM NEW_FEEDS WHERE SUBJECTID='%@' AND SUBJECTINDEX>%d AND SUBJECTINDEX<=%d  ORDER BY SUBJECTINDEX DESC;",subjectId,from,to] autorelease];
    return selectSql;
}
+ (NSMutableArray *)selectFeedsWithSql:(NSString *)sql{
    sqlite3 *database = [SqlUtil openDatabse];
	NSMutableArray *feeds = [[[NSMutableArray alloc] init] autorelease];
	
	sqlite3_stmt *statement;
	if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil) == SQLITE_OK){
		while (sqlite3_step(statement) == SQLITE_ROW) {
			Feed *feed = [[Feed alloc] init];
			feed.feedId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
            feed.sectionIndex = sqlite3_column_int64(statement, 1);
            feed.subjectIndex = sqlite3_column_int64(statement, 2);
			feed.title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
			feed.author = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
			feed.pubDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
			feed.createDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
			feed.description = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement,7)];
			feed.link = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
			NSInteger complete =  sqlite3_column_int64(statement, 9);
			if (complete == 1) {
				feed.complete = YES;
			}else {
				feed.complete = NO;
			}
			feed.thumb = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
			[feeds addObject:feed];
			[feed release];
		}
		
		sqlite3_finalize(statement);
		sqlite3_close(database);
	}else {
		sqlite3_finalize(statement);
		sqlite3_close(database);
		NSAssert(0,@"Error select feeds");
	}
	return feeds;
}


+ (NSString *) buildCountFeedSqlWithSection:(NSString *)sectionId from:(long) from to:(long) to{
    NSString *selectSql = [[[NSString alloc] initWithFormat:@"SELECT COUNT(1) FROM NEW_FEEDS WHERE SECTIONID='%@' AND SECTIONINDEX>%d AND SECTIONINDEX<=%d ",sectionId,from,to] autorelease];

    return selectSql;
}
+ (NSString *) buildCountFeedSqlWithSubject:(NSString *)subjectId from:(long) from to:(long) to{
    NSString *selectSql = [[[NSString alloc] initWithFormat:@"SELECT COUNT(1) FROM NEW_FEEDS WHERE SUBJECTID='%@' AND SUBJECTINDEX>%d AND SUBJECTINDEX<=%d ",subjectId,from,to] autorelease];
    
    return selectSql;
}
+ (NSInteger)countFeedsWithSectionId:(NSString *)sql{
    sqlite3 *database = [SqlUtil openDatabse];
	NSInteger count = 0;
	sqlite3_stmt *statement;
	if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil ) == SQLITE_OK){
		while (sqlite3_step(statement) == SQLITE_ROW) {
			count = sqlite3_column_int(statement, 0);
		}
		sqlite3_finalize(statement);
		sqlite3_close(database);
	}else {
		sqlite3_finalize(statement);
		sqlite3_close(database);
		//NSAssert(0,@"Error select feeds");
		return 0;
	}
	
	return count;

}

+ (NSString *) buildSelectFeedSqlWithSection:(NSString *)sectionId startFeedId:(NSInteger)startFeedId andNeedCount:(NSInteger)count{
	NSString *selectSql = [[NSString alloc] initWithFormat:@"SELECT FEEDID,SECTIONINDEX,SUBJECTINDEX,TITLE,AUTHOR,PUBDATE,CREATEDATE,DESCRIPTION,LINK,COMPLETE, THUMB FROM NEW_FEEDS WHERE SECTIONID='%@' AND SECTIONINDEX<=%d  ORDER BY SECTIONINDEX DESC LIMIT 0,%d;",sectionId,startFeedId,count];
    return selectSql;
}
+ (NSString *) buildSelectFeedSqlWithSubject:(NSString *)subjectId startFeedId:(NSInteger)startFeedId andNeedCount:(NSInteger)count{
    NSString *selectSql = [[NSString alloc] initWithFormat:@"SELECT FEEDID,SECTIONINDEX,SUBJECTINDEX,TITLE,AUTHOR,PUBDATE,CREATEDATE,DESCRIPTION,LINK,COMPLETE, THUMB FROM NEW_FEEDS WHERE SUBJECTID='%@' AND SUBJECTINDEX<=%d  ORDER BY SUBJECTINDEX DESC LIMIT 0,%d;",subjectId,startFeedId,count];
    return selectSql;

}


+ (NSString *)buildSelectMaxIndexWithSectionId:(NSString *)sectionId{
    NSString *selectSql = [[[NSString alloc] initWithFormat:@"SELECT MAX(SECTIONINDEX) FROM NEW_FEEDS WHERE SECTIONID='%@'",sectionId] autorelease];
    return selectSql;
}
+ (NSString *)buildSelectMaxIndexWithSubjectId:(NSString *)subjectId{
    NSString *selectSql = [[[NSString alloc] initWithFormat:@"SELECT MAX(SUBJECTINDEX) FROM NEW_FEEDS WHERE SUBJECTID='%@'",subjectId] autorelease];
    return selectSql;
}

+ (NSInteger)selectMaxIndexWithSectionOrSubject:(NSString *)sql{
    sqlite3 *database = [SqlUtil openDatabse];
	
	NSInteger maxId = 0;
	sqlite3_stmt *statement;
	
	
	if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil ) == SQLITE_OK){
		while (sqlite3_step(statement) == SQLITE_ROW) {
			maxId = sqlite3_column_int(statement, 0);
		}
		sqlite3_finalize(statement);
		sqlite3_close(database);
	}else {
		sqlite3_finalize(statement);
		sqlite3_close(database);
		//NSAssert(0,@"Error select feeds");
		return 0;
	}
	
	//NSLog(@"maxid = %d",maxId);
	return maxId;

}







+ (NSArray *)selectContentsWithFeed:(Feed *)feed{
	sqlite3 *database = [SqlUtil openDatabse];
	NSString *selectSql = [[NSString alloc] initWithFormat:@"SELECT FEEDID,HEIGTH,WIDTH,TYPE,SRC,CONTENT FROM NEW_CONTENTS WHERE FEEDID='%@'",feed.feedId];
	NSMutableArray *contents = [[[NSMutableArray alloc] init] autorelease];
	
	sqlite3_stmt *statement;
	//sqlite3_p
	if(sqlite3_prepare_v2(database, [selectSql UTF8String], -1, &statement, nil) == SQLITE_OK){
		while (sqlite3_step(statement) == SQLITE_ROW) {
			Content  *content = [[Content alloc] init];
			content.feedId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)];
			content.heigth = sqlite3_column_int64(statement, 1);
			content.width = sqlite3_column_int64(statement, 2);
			content.type = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
			content.content = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
			
			[contents addObject:content];
			[content release];
		}
		sqlite3_finalize(statement);
		sqlite3_close(database);
	}else {
		sqlite3_finalize(statement);
		sqlite3_close(database);
		NSAssert(0,@"Error select feeds");
	}
	
	[selectSql release];
	return contents;
	
}
//将更新feed，删除老的content，新增新的content放在一起，在一个事务中进行
+(void)saveOrUpdateFeedsAndItsContent:(NSArray *)feeds{

	if ([feeds count]==0) {
		return;
	}
	sqlite3 *database = [SqlUtil openDatabse];
	sqlite3_exec(database,"BEGIN TRANSACTION",0,0,0); 
 
	
	static char *deleteContent = "DELETE  FROM NEW_CONTENTS WHERE FEEDID =? ";
	
	static char *insertFeed = 
		"INSERT OR REPLACE INTO NEW_FEEDS(FEEDID,SECTIONINDEX,SUBJECTINDEX,SECTIONID,SUBJECTID,LAYOUTSCALE,TITLE,AUTHOR,PUBDATE,CREATEDATE,DESCRIPTION,LINK,COMPLETE,THUMB,INSERTDATE) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	static char *insertContent = 
		"INSERT  INTO NEW_CONTENTS(CONTENTID,FEEDID,HEIGTH,WIDTH,TYPE,SRC,CONTENT,INSERTDATE) VALUES(null,?,?,?,?,?,?,?)";
	
	NSDate *date = [NSDate date];
	NSInteger timeInterval = [date timeIntervalSince1970];
	
	
	for(Feed *feed in feeds) {
		//更新feeds开始
		sqlite3_stmt *statement1;
		if (sqlite3_prepare_v2(database, insertFeed, -1, &statement1, nil) != SQLITE_OK) {
			sqlite3_close(database);
			NSAssert(0,@"Error: failed to sqlite3_prepare_v2");
		}
        sqlite3_bind_text(statement1, 1, [feed.feedId UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_int64(statement1, 2,feed.sectionIndex );
        sqlite3_bind_int64(statement1, 3,feed.subjectIndex );
        sqlite3_bind_text(statement1, 4, [feed.sectionId UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement1, 5, [feed.subjectId UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_int64(statement1, 6,feed.layoutScale );
		sqlite3_bind_text(statement1, 7, [feed.title UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement1, 8, [feed.author UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement1, 9,[feed.pubDate UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement1, 10,[feed.createDate UTF8String], -1, SQLITE_TRANSIENT );
		sqlite3_bind_text(statement1, 11, [feed.description UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(statement1, 12, [feed.link UTF8String], -1, SQLITE_TRANSIENT);
		if (feed.createDate) {
			sqlite3_bind_int64(statement1, 13,1);
		}else {
			sqlite3_bind_int64(statement1, 13,0);
		}
		sqlite3_bind_text(statement1, 14, [feed.thumb UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_int64(statement1, 15,timeInterval );
		
		//int code = sqlite3_step(statement1);
		//NSLog(@"%d",code);
		if (sqlite3_step(statement1) != SQLITE_DONE) {
			sqlite3_finalize(statement1);
			sqlite3_close(database);
			//NSAssert(0,@"Error: failed to insert or update feed %@",feed.title);
		}
		sqlite3_finalize(statement1);
		//更新feeds结束
        
        
        //删除feeds对应的contents
        sqlite3_stmt *statement2;
        if (sqlite3_prepare_v2(database, deleteContent, -1, &statement2, nil) != SQLITE_OK) {
            sqlite3_close(database);
            NSAssert(0,@"Error: failed to sqlite3_prepare_v2");
        }
        sqlite3_bind_text(statement2, 1, [feed.feedId UTF8String], -1, SQLITE_TRANSIENT);
        if (sqlite3_step(statement2) == SQLITE_ERROR) {
            sqlite3_finalize(statement2);
            sqlite3_close(database);
            NSAssert(0,@"Error: failed to insert or update feed");
        }
        sqlite3_finalize(statement2);
        //删除contents结束
        
		
		//新增content
		for (Content *content in feed.details) {
			
			sqlite3_stmt *statement;
			if (sqlite3_prepare_v2(database, insertContent, -1, &statement, nil) != SQLITE_OK) {
				sqlite3_close(database);
				NSAssert(0,@"Error: failed to sqlite3_prepare_v2");
			}
            sqlite3_bind_text(statement, 1, [content.feedId UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_int64(statement, 2,content.heigth );
			sqlite3_bind_int64(statement, 3, content.width);
			sqlite3_bind_text(statement, 4, [content.type UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 5, [content.src UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(statement, 6,[content.content UTF8String], -1, SQLITE_TRANSIENT);
			sqlite3_bind_int64(statement, 7,timeInterval );
			
			if (sqlite3_step(statement) != SQLITE_DONE) {
				sqlite3_finalize(statement);
				sqlite3_close(database);
				//NSAssert(0,@"Error: failed to insert content %@",content.content);
			}
			sqlite3_finalize(statement);
		}
		//新增content结束
		
	}
	
	sqlite3_exec(database,"COMMIT",0,0,0); 
	sqlite3_close(database);
	
}
+(void)deleteFeedsAndItsContentsEalierThen:(NSDate *)date{
	//删除feeds对应的contents
	static char *deleteFeeds = "DELETE  FROM NEW_FEEDS WHERE INSERTDATE <= ?";
	static char *deleteContents = "DELETE  FROM NEW_CONTENTS WHERE INSERTDATE <= ?";
	
	sqlite3 *database = [SqlUtil openDatabse];
	
	sqlite3_stmt *statement1;
	if (sqlite3_prepare_v2(database, deleteFeeds, -1, &statement1, nil) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0,@"Error: failed to sqlite3_prepare_v2");
	}
	sqlite3_bind_int64(statement1, 1,[date timeIntervalSince1970]);
	
	if (sqlite3_step(statement1) == SQLITE_ERROR) {
		sqlite3_finalize(statement1);
		sqlite3_close(database);
		NSAssert(0,@"Error: failed to insert or update feed");
	}
	sqlite3_finalize(statement1);
	
	sqlite3_stmt *statement2;
	if (sqlite3_prepare_v2(database, deleteContents, -1, &statement2, nil) != SQLITE_OK) {
		sqlite3_close(database);
		NSAssert(0,@"Error: failed to sqlite3_prepare_v2");
	}
	sqlite3_bind_int64(statement2, 1,[date timeIntervalSince1970]);
	
	if (sqlite3_step(statement2) == SQLITE_ERROR) {
		sqlite3_finalize(statement2);
		sqlite3_close(database);
		NSAssert(0,@"Error: failed to insert or update feed");
	}

	
	
	sqlite3_close(database);
}
@end
